﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;

namespace 代码生成工具
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        string connstr;
        SqlConnection sconn;
        private void TestConnectBtn_Click(object sender, RoutedEventArgs e)
        {
            //string connstr = @"Data Source=localhost;Initial Catalog=myWeb;Persist Security Info=True;User ID=test;Password=test";
            connstr = @"Data Source=" + DataAddrTxtBox.Text + ";Initial Catalog=master;Persist Security Info=True;User ID=" + UsernameTxtBox.Text + ";Password=" + PswTxtBox.Text;
            string sql = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
            sconn = new SqlConnection(connstr);
            try
            {
                sconn.Open();
                SqlCommand sc = sconn.CreateCommand();
                sc.CommandText = sql;
                SqlDataReader sdr = sc.ExecuteReader();
                while (sdr.Read()) 
                {
                    DbNameCbox.Items.Add(sdr["Name"]);
                }
                sdr.Close();
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message);
            }
            finally 
            {
                
                sconn.Close();
            }
            DbNameCbox.SelectedIndex = 0;
        }

        private void DbNameCbox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            //if(DbNameCbox.sel)
            //MessageBox.Show(DbNameCbox.SelectedValue.ToString());
            TableNameCbox.Items.Clear();
            connstr = @"Data Source=" + DataAddrTxtBox.Text + ";Initial Catalog=" + DbNameCbox.SelectedValue.ToString() + ";Persist Security Info=True;User ID=" + UsernameTxtBox.Text + ";Password=" + PswTxtBox.Text;
            string sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name";
            sconn = new SqlConnection(connstr);
            try
            {
                sconn.Open();
                SqlCommand sc = sconn.CreateCommand();
                sc.CommandText = sql;
                SqlDataReader sdr = sc.ExecuteReader();
                while (sdr.Read())
                {
                    TableNameCbox.Items.Add(sdr["Name"]);
                }
                sdr.Close();
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message);
            }
            finally
            {

                sconn.Close();
            }
            TableNameCbox.SelectedIndex = 0;
        }
        private void ToModelBtn_Click(object sender, RoutedEventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            DataTable tb = new DataTable();
            
            //string sql = "SELECT Name FROM SysColumns WHERE id=Object_Id('" + TableNameCbox.SelectedItem + "')";
            string sql = "select top 0 * from " + TableNameCbox.SelectedValue;
            connstr = @"Data Source=" + DataAddrTxtBox.Text + ";Initial Catalog=" + DbNameCbox.SelectedValue.ToString() + ";Persist Security Info=True;User ID=" + UsernameTxtBox.Text + ";Password=" + PswTxtBox.Text;
            sconn = new SqlConnection(connstr);
            try
            {
                sconn.Open();
                SqlCommand sc = sconn.CreateCommand();
                sc.CommandText = sql;
                SqlDataAdapter sda = new SqlDataAdapter(sc);
                DataSet ds = new DataSet();
                sda.FillSchema(ds,SchemaType.Source);
                sda.Fill(ds);
                tb = ds.Tables[0];
                sb.AppendLine("public class " + TableNameCbox.SelectedValue + "Model");
                sb.AppendLine("{");
                for (int i = 0; i < tb.Columns.Count; i++) 
                {
                    sb.AppendLine("    private " + MyType(tb.Columns[i].DataType) + ((tb.Columns[i].AllowDBNull) ? MyTypeEnableNull(MyType(tb.Columns[i].DataType)) : "") + " _" + tb.Columns[i].ColumnName + ";");
                    sb.AppendLine("    public " + MyType(tb.Columns[i].DataType) + ((tb.Columns[i].AllowDBNull) ? MyTypeEnableNull(MyType(tb.Columns[i].DataType)) : "") + " " + tb.Columns[i].ColumnName + "");
                    sb.AppendLine("    {");
                    sb.AppendLine("        get{return this._" + tb.Columns[i].ColumnName + ";}");
                    sb.AppendLine("        set{this._" + tb.Columns[i].ColumnName + " = value;}");
                    sb.AppendLine("    }");
                }
                sb.AppendLine("}");
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message);
            }
            finally
            {

                sconn.Close();
            }
            CodeTxtBox.Text = sb.ToString();
        }

        private void ToDALBtn_Click(object sender, RoutedEventArgs e)
        {
            string sql = "select top 0 * from " + TableNameCbox.SelectedValue;
            connstr = @"Data Source=" + DataAddrTxtBox.Text + ";Initial Catalog=" + DbNameCbox.SelectedValue.ToString() + ";Persist Security Info=True;User ID=" + UsernameTxtBox.Text + ";Password=" + PswTxtBox.Text;
            sconn = new SqlConnection(connstr);
            List<string> parmList = new List<string>();
            List<string> sqlparmList = new List<string>();
            List<string> sqlparmObjList = new List<string>();
            List<string> updatesqlList = new List<string>();
            List<string> parmType = new List<string>();
            List<bool> parmEnableNull = new List<bool>();
            string KeyColumnName="未设置主键";
            try
            {
                sconn.Open();
                SqlCommand sc = sconn.CreateCommand();
                sc.CommandText = sql;
                SqlDataAdapter sda = new SqlDataAdapter(sc);
                DataSet ds = new DataSet();
                sda.FillSchema(ds, SchemaType.Source);
                sda.Fill(ds);
                DataTable tb = ds.Tables[0];
                for (int i = 0; i < tb.Columns.Count; i++)
                {
                    parmList.Add(tb.Columns[i].ColumnName);
                    sqlparmList.Add("@" + tb.Columns[i].ColumnName);
                    sqlparmObjList.Add("                               new  SqlParameter(\"" + tb.Columns[i].ColumnName + "\", DBHelper.ToDbValue(o." + tb.Columns[i].ColumnName + "))");
                    updatesqlList.Add("        sqlstr.Append(\"" + tb.Columns[i].ColumnName + "=@" + tb.Columns[i].ColumnName + ",\")");
                    parmType.Add(tb.Columns[i].DataType.Name);
                    parmEnableNull.Add(tb.Columns[i].AllowDBNull);
                    KeyColumnName = tb.PrimaryKey[0].ColumnName;
                }
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message);
            }
            finally
            {
                
                sconn.Close();
            }
            
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("public class " + TableNameCbox.SelectedValue + "DAL");
            sb.AppendLine("{");
            //begin
            sb.AppendLine("    private const string sql_select = \"select " + string.Join(",", parmList.ToArray()) + " from " + TableNameCbox.SelectedValue + " \";");
            //insert
            sb.AppendLine("    public static int insert(" + TableNameCbox.SelectedValue + "Model o)");
            sb.AppendLine("    {");
            sb.AppendLine("        StringBuilder sqlstr = new StringBuilder();");
            sb.AppendLine("        sqlstr.Append(\"insert into " + TableNameCbox.SelectedValue + "(" + string.Join(",", parmList.ToArray()) + ") values(" + string.Join(",", sqlparmList.ToArray()) + ")\");");
            sb.AppendLine("        SqlParameter[] Parms = {");
            sb.AppendLine(string.Join(",\r\n",sqlparmObjList.ToArray()));
            sb.AppendLine("        };");
            sb.AppendLine("        return DBHelper.ExecuteCommand(sqlstr.ToString(),Parms);");
            sb.AppendLine("    }");
            //del
            sb.AppendLine("    public static int del(int " + KeyColumnName + ")");
            sb.AppendLine("    {");
            sb.AppendLine("        StringBuilder sqlstr=new StringBuilder();");
            sb.AppendLine("        sqlstr.Append(\"delete from " + TableNameCbox.SelectedValue + " where " + KeyColumnName + "=@" + KeyColumnName + "\");");
            sb.AppendLine("        SqlParameter[] Parms = { new SqlParameter(\"" + KeyColumnName + "\", " + KeyColumnName + ") };");
            sb.AppendLine("        return DBHelper.ExecuteCommand(sqlstr.ToString(),Parms);");
            sb.AppendLine("    }");
            //update
            sb.AppendLine("    public static int update(" + TableNameCbox.SelectedValue +"Model o)");
            sb.AppendLine("    {");
            sb.AppendLine("        StringBuilder sqlstr=new StringBuilder();");
            sb.AppendLine("        sqlstr.Append(\"update " + TableNameCbox.SelectedValue + " set \");");
            sb.Append(string.Join(";\r\n",updatesqlList.ToArray()));
            sb.Replace(",\"", "\"", sb.Length-4, 4);
            sb.AppendLine(";");
            sb.AppendLine("        sqlstr.Append(\" where " + KeyColumnName + "=@" + KeyColumnName + "\");");
            sb.AppendLine("        SqlParameter[] Parms ={");
            sb.AppendLine(string.Join(",\r\n", sqlparmObjList.ToArray()));
            sb.AppendLine("        };");
            sb.AppendLine("        return DBHelper.ExecuteCommand(sqlstr.ToString(),Parms);");
            sb.AppendLine("    }");
            //getById
            sb.AppendLine("    public static " + TableNameCbox.SelectedValue + "Model getById(int " + KeyColumnName + ")");
            sb.AppendLine("    {");
            sb.AppendLine("        " + TableNameCbox.SelectedValue + "Model o = new " + TableNameCbox.SelectedValue + "Model();");
            sb.AppendLine("        StringBuilder sqlstr = new StringBuilder();");
            sb.AppendLine("        sqlstr.Append(sql_select);");
            sb.AppendLine("        sqlstr.Append(\"where " + KeyColumnName + "=@" + KeyColumnName + "\");");
            sb.AppendLine("        SqlParameter[] Parms = { new SqlParameter(\"" + KeyColumnName + "\", " + KeyColumnName + ") };");
            sb.AppendLine("        DataTable dt = DBHelper.GetDataSet(sqlstr.ToString(),Parms);");
            sb.AppendLine("        if (dt.Rows.Count == 0)");
            sb.AppendLine("        {");
            sb.AppendLine("            return null;");
            sb.AppendLine("        }");
            sb.AppendLine("        return To" + TableNameCbox.SelectedValue + "Model(dt.Rows[0]); ");
            sb.AppendLine("    }");
            //getAll
            sb.AppendLine("    public static IList<" + TableNameCbox.SelectedValue + "Model> getAll()");
            sb.AppendLine("    {");
            sb.AppendLine("        IList<" + TableNameCbox.SelectedValue + "Model> " + TableNameCbox.SelectedValue + "Modellist = new List<" + TableNameCbox.SelectedValue + "Model>();");
            sb.AppendLine("        string sql = \"select * from " + TableNameCbox.SelectedValue + " \";");
            sb.AppendLine("        DataTable dt = DBHelper.GetDataSet(sql);");
            sb.AppendLine("        for (int i = 0; i < dt.Rows.Count; i++) ");
            sb.AppendLine("        {");
            sb.AppendLine("            " + TableNameCbox.SelectedValue + "Modellist.Add(To" + TableNameCbox.SelectedValue + "Model(dt.Rows[i]));");
            sb.AppendLine("        }");
            sb.AppendLine("        return " + TableNameCbox.SelectedValue + "Modellist;");
            sb.AppendLine("    }");
            //ToModel
            sb.AppendLine("    public static " +  TableNameCbox.SelectedValue + "Model To" + TableNameCbox.SelectedValue + "Model(DataRow dr) ");
            sb.AppendLine("    {");
            sb.AppendLine("        " + TableNameCbox.SelectedValue + "Model model = new " + TableNameCbox.SelectedValue + "Model();");
            for(int i=0;i<parmList.Count;i++)
            {
                sb.AppendLine("        model." + parmList[i] + " = " + ToModelString("dr[\"" + parmList[i] + "\"]", parmType[i]) + ";");
            }
            sb.AppendLine("        return model;");
            sb.AppendLine("    }");
            //end
            sb.AppendLine("}");
            CodeTxtBox.Text = sb.ToString();

        }
        private void ToInterfaceBtn_Click(object sender, RoutedEventArgs e)
        {
            string sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='" + TableNameCbox.SelectedValue + "'";
            connstr = @"Data Source=" + DataAddrTxtBox.Text + ";Initial Catalog=" + DbNameCbox.SelectedValue.ToString() + ";Persist Security Info=True;User ID=" + UsernameTxtBox.Text + ";Password=" + PswTxtBox.Text;
            sconn = new SqlConnection(connstr);
            string KeyColumName = "请设置主键";
            string KeyColumType = "int";
            try
            {
                SqlCommand sc = sconn.CreateCommand();
                sc.CommandText = sql;
                sconn.Open();
                KeyColumName = sc.ExecuteScalar().ToString();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
            finally 
            {
                sconn.Close();
            }

            //获取主键字段类型
            if (KeyColumName != "请设置主键") 
            {
                try
                {
                    sconn.Open();
                    SqlCommand sc = sconn.CreateCommand();
                    sc.CommandText = "select top 0 * from " + TableNameCbox.SelectedValue;
                    SqlDataAdapter sda = new SqlDataAdapter(sc);
                    DataSet ds = new DataSet();
                    sda.FillSchema(ds, SchemaType.Source);
                    sda.Fill(ds);
                    DataTable tb = ds.Tables[0];
                    KeyColumType = tb.Columns[KeyColumName].DataType.Name;
                }
                catch (SqlException ee)
                {
                    MessageBox.Show(ee.Message);
                }
                finally
                {

                    sconn.Close();
                }

            }
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("public interface I" + TableNameCbox.SelectedValue);
            sb.AppendLine("{");
            sb.AppendLine("    int insert(" + TableNameCbox.SelectedValue + "Model o);");
            sb.AppendLine("    int del(" + KeyColumType + " " + KeyColumName + ");");
            sb.AppendLine("    int update(" + TableNameCbox.SelectedValue + "Model o);");
            sb.AppendLine("    " + TableNameCbox.SelectedValue + "Model getById(" + KeyColumType + " " + KeyColumName + ");");
            sb.AppendLine("    IList<" + TableNameCbox.SelectedValue + "Model> getAll();");
            sb.AppendLine("}");
            CodeTxtBox.Text = sb.ToString();
        }
        public string ToModelString(string sourse, string t)
        {
            switch (t)
            {
                case "Int16":
                    return "Convert.ToInt16(DBHelper.FromDbValue(" + sourse + "))";
                case "Int64":
                    return "Convert.ToInt64(DBHelper.FromDbValue(" + sourse + "))";
                case "Int32":
                    return "Convert.ToInt32(DBHelper.FromDbValue(" + sourse + "))";
                case "String":
                    return sourse + ".ToString()";
                default:
                    return "DBHelper.FromDbValue(" + sourse + ")";
            }
        }

        private string MyType(Type t) 
        {
            switch (t.Name) 
            {
                case "Int16":
                    return "Int16";
                case "Int64":
                    return "Int64";
                case "Int32":
                    return "Int32";
                case "String":
                    return "String";
                default:
                    return "object";
            }
        }
        private string MyTypeEnableNull(string type) 
        {
            switch (type.ToLower())
            {
                case "string":
                    return "";
                case "object":
                    return "";
                default:
                    return "?";
            }
        }

        private void TableNameCbox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            ToModelBtn.IsEnabled = true;
            ToDALBtn.IsEnabled = true;
            ToInterfaceBtn.IsEnabled = true;
        }
    }
}
